USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[AssistsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.AssistsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Assists Table Insert
-- =============================================
CREATE PROCEDURE AssistsInsert

	@AssistId int,
	@AssistTime datetime,
	@KillId int,
	@Assister_playerId varchar(30),
	@Victim_playerId varchar(30),
	@Attacker_position_x float,
	@Attacker_position_y float,
	@Attacker_position_z float,
	@Assister_position_x float,
	@Assister_position_y float,
	@Assister_position_z float,
	@Victim_position_x float,
	@Victim_position_y float,
	@Victim_position_z float,
	@LogId int,
	@RoundId int,
	@MiniRoundId int,
	@MapId int,
	@ClassId int,
	@AssistType varchar(30)
	

AS
BEGIN

	INSERT INTO [Assists] (AssistId, AssistTime, KillId, Assister_playerId, Victim_playerId, 
			Attacker_position_x, Attacker_position_y, Attacker_position_z,
			Assister_position_x, Assister_position_y, Assister_position_z,
			Victim_position_x, Victim_position_y, Victim_position_z, LogId,
			RoundId, MiniRoundId, MapId, ClassId, AssistType)
	VALUES
		(@AssistId, @AssistTime, @KillId, @Assister_playerId, @Victim_playerId, 
			@Attacker_position_x, @Attacker_position_y, @Attacker_position_z,
			@Assister_position_x, @Assister_position_y, @Assister_position_z,			
			@Victim_position_x, @Victim_position_y, @Victim_position_z, @LogId,
			@RoundId, @MiniRoundId, @MapId, @ClassId, @AssistType)

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[AssistsSelectByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.AssistsSelectByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	PlayerStatsTeam Table Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE AssistsSelectByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@AssistId int = 0 OUTPUT
	
AS

BEGIN

select @AssistId = COUNT(AssistId) FROM Assists WHERE LogId = @LogId AND assister_PlayerId = @PlayerId


END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[AssistsSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.AssistsSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Assists Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE AssistsSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@AssistCount int = 0 OUTPUT
	
AS

BEGIN

select @AssistCount = COUNT(AssistId) FROM Assists WHERE LogId = @LogId AND assister_PlayerId = @PlayerId


END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[AssistsSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.AssistsSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Assists Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE AssistsSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@AssistCount int = 0 OUTPUT
	
AS

BEGIN

select @AssistCount = COUNT(AssistId) FROM Assists WHERE LogId = @LogId AND assister_PlayerId = @PlayerId


END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CaptureBlocksInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.CaptureBlocksInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	CaptureBlocks Table Insert
-- =============================================
CREATE PROCEDURE CaptureBlocksInsert

	@CaptureBlockId int,
	@CaptureBlockTime datetime,
	@CapNumber int,
	@CapName varchar(30),
	@LogId int,
	@RoundId int,
	@MiniRoundId int,
	@MapId int,
	@PlayerId varchar(30)
	

AS
BEGIN

	INSERT INTO [CaptureBlocks] (	
			CaptureBlockId ,
			CaptureBlockTime,
			CapNumber,
			CapName ,
			LogId ,
			RoundId ,
			MiniRoundId ,
			MapId ,
			PlayerId 
		  )
	VALUES(	
			@CaptureBlockId ,
			@CaptureBlockTime,
			@CapNumber,
			@CapName ,
			@LogId ,
			@RoundId ,
			@MiniRoundId ,
			@MapId ,
			@PlayerId 
		  )

END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CaptureBlocksSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.CaptureBlocksSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	CaptureBlocks Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE CaptureBlocksSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@CaptureBlocksCount int = 0 OUTPUT
	
AS

BEGIN

select @CaptureBlocksCount = COUNT(CaptureBlockId) FROM CaptureBlocks 
		WHERE LogId = @LogId
		AND PlayerId = @PlayerId

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CaptureListInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.CaptureListInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	CaptureList Table Insert
-- =============================================
CREATE PROCEDURE CaptureListInsert

	@CaptureListId int,
	@CaptureId int,
	@PlayerId varchar(30)
	

AS
BEGIN

	INSERT INTO [CaptureList] (	
			CaptureListId,
			CaptureId,
			PlayerId 
		  )
	VALUES(	
			@CaptureListId,
			@CaptureId,
			@PlayerId 
		  )

END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CaptureListSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.CaptureListSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Captures Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE CaptureListSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@CaptureListCount int = 0 OUTPUT
	
AS

BEGIN

select @CaptureListCount = COUNT(CaptureListId) FROM CaptureList 
		WHERE CaptureId IN (SELECT CaptureId FROM Captures WHERE LogId = @LogId)
		AND PlayerId = @PlayerId


END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CapturesInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.CapturesInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Captures Table Insert
-- =============================================
CREATE PROCEDURE CapturesInsert

			@CaptureId int,
			@CaptureTime datetime,
            @CapName varchar(30),
            @CapNumber int,
            @RoundId int,
            @MiniRoundId int,
            @MapId int,
            @LogId int,
            @TeamId int
	

AS
BEGIN

INSERT INTO [Captures]
           ([CaptureId]
           ,[CaptureTime]
           ,[CapName]
           ,[CapNumber]
           ,[RoundId]
           ,[MiniRoundId]
           ,[MapId]
           ,[LogId]
           ,[TeamId])
     VALUES
           (@CaptureId,
			@CaptureTime,
			@CapName,
			@CapNumber,
			@RoundId,
			@MiniRoundId,
			@MapId,
			@LogId,
			@TeamId
			)

END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CapturesSelectTop5Players]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.CapturesSelectTop5Players
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Captures Table Select - Top 5 Captures
-- =============================================
CREATE PROCEDURE CapturesSelectTop5Players
	
AS

BEGIN

SELECT Top 5 PlayerName, COUNT(CaptureList.PlayerId) AS [Captures], CaptureList.PlayerId
FROM CaptureList INNER JOIN PlayerNameList on CaptureList.PlayerId = PlayerNameList.PlayerId
GROUP BY CaptureList.PlayerId, PlayerName
ORDER BY [Captures] DESC

END
GO



--  CapturesSelectTop5Players


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ChatLogInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.ChatLogInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	ChatLog Table Insert
-- =============================================
CREATE PROCEDURE ChatLogInsert

		   @ChatLogId int,
           @PlayerId varchar(30),
           @TeamId int,
           @Text text,
           @DateTime datetime,
           @LogId int,
           @RoundId int,
           @MiniRoundId int,
           @ChatType varchar(20)
	

AS
BEGIN

INSERT INTO [ChatLog]
           ([ChatLogId]
		  ,[PlayerId]
		  ,[TeamId]
		  ,[Text]
		  ,[DateTime]
		  ,[LogId]
		  ,[RoundId]
		  ,[MiniRoundId]
		  ,[ChatType])
     VALUES
           (@ChatLogId,
           @PlayerId,
           @TeamId,
           @Text,
           @DateTime,
           @LogId,
           @RoundId,
           @MiniRoundId,
           @ChatType)

END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ChatLogSelectByLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.ChatLogSelectByLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	ChatLog Table Select Count By LogId
-- =============================================
CREATE PROCEDURE ChatLogSelectByLogId

            @LogId int
	
AS

BEGIN

	SELECT * FROM ChatLog
	WHERE LogId = @LogId
	ORDER BY DateTime

END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ClassesHeadshotCountSelect]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.ClassesHeadshotCountSelect
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/24/08
-- Description:	Classes Table Kill Count Select
-- =============================================
CREATE PROCEDURE ClassesHeadshotCountSelect

	@ClassId int

AS

BEGIN

	SELECT COUNT(Kills.Attacker_classId) AS [Kills] FROM Kills
	WHERE Kills.Attacker_classId = @ClassId
	AND CustomKillId = (SELECT CustomKillId FROM customkills
			WHERE CustomKillShortName = 'headshot')

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ClassesInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.ClassesInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Classes Table Insert
-- =============================================
CREATE PROCEDURE ClassesInsert

           @ClassId int,
           @ClassShortName varchar(30),
           @ClassLongName varchar(50)
	

AS
BEGIN

INSERT INTO [Classes]
           ([ClassId]
           ,[ClassShortName]
           ,[ClassLongName])
     VALUES
           (@ClassId,
            @ClassShortName,
            @ClassLongName)

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ClassesSelectTop25Stats]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.ClassesSelectTop25Stats
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/24/08
-- Description:	Classes Table Top 25 Stats Select
-- =============================================
CREATE PROCEDURE ClassesSelectTop25Stats

	@ClassId int

AS

BEGIN


	DECLARE @PlayerId varchar(30)

DECLARE @PlayerName varchar(50),
		@Kills int,
		@Deaths int,
		@HeadShotPerctage float


DECLARE @tempPlayers TABLE
(
	PlayerName varchar(50),
	Kills int,
	Deaths int,
	KillDeathRatio varchar(5),
	HeadShotPercentage float,
	PlayerId varchar(30)
)

DECLARE c1 CURSOR FAST_FORWARD
FOR

SELECT Top 25 COUNT(KillId) [Kills], Attacker_playerId
FROM Kills
WHERE Attacker_classId = @ClassId
GROUP BY Attacker_playerId
ORDER BY [Kills] Desc

OPEN c1

FETCH NEXT FROM c1
INTO @Kills, @PlayerId


WHILE @@FETCH_STATUS = 0
BEGIN

	SELECT @PlayerName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @PlayerId 
	GROUP BY PlayerName

	
	SELECT @Deaths = COUNT(KillId) FROM Kills WHERE Victim_playerId = @PlayerId
						AND Attacker_classId = @ClassId

	INSERT INTO @tempPlayers VALUES (@PlayerName, @Kills, @Deaths, 0.0, 0.0, @PlayerId)

	FETCH NEXT FROM c1
	INTO @Kills, @PlayerId

END

CLOSE c1
DEALLOCATE c1

SELECT PlayerName, Kills, Deaths, KillDeathRatio, HeadShotPercentage, PlayerId FROM @tempPlayers
ORDER BY Kills DESC, KillDeathRatio DESC, Deaths DESC
	
END
GO


-- ClassesSelectTop25Stats 2

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ClassesStatsSelect]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.ClassesStatsSelect
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/24/08
-- Description:	Classes Table Stats Select
-- =============================================
CREATE PROCEDURE ClassesStatsSelect

AS

BEGIN

	SELECT COUNT(Kills.Attacker_classId) AS [Kills], 0 AS [Deaths], 0.0 AS [KillDeathRatio], 
	0.0 AS [Headshot Percentage], ClassId, ClassShortName, ClassLongName FROM Classes
	LEFT OUTER JOIN Kills on Kills.Attacker_classId = Classes.ClassId
	GROUP BY ClassId, ClassShortName, ClassLongName
	ORDER BY [Kills] DESC
	
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CustomKillInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.CustomKillInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	CustomKill Table Insert
-- =============================================
CREATE PROCEDURE CustomKillInsert

            @CustomKillId int,
            @CustomKillShortName varchar(30),
            @CustomKillFullName varchar(50)
	

AS
BEGIN

INSERT INTO [CustomKills]
           ([CustomKillId]
           ,[CustomKillShortName]
           ,[CustomKillFullName])
     VALUES
           (@CustomKillId,
            @CustomKillShortName,
            @CustomKillFullName)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[CustomKillSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.CustomKillSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	CustomKills Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE CustomKillSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@CustomKillType varchar(30),
			@CustomKillCount int = 0 OUTPUT
	
AS

BEGIN

select @CustomKillCount = COUNT(KillId) FROM Kills
		WHERE LogId = @LogId
		AND Attacker_PlayerId = @PlayerId
		AND CustomKillId = (SELECT CustomKillId FROM CustomKills WHERE CustomKillShortName = @CustomKillType)


END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeathsSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.DeathsSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Kills Table Deaths Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE DeathsSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@DeathCount int = 0 OUTPUT
	
AS

BEGIN

select @DeathCount = COUNT(KillId) FROM Kills WHERE LogId = @LogId AND Victim_PlayerId = @PlayerId


END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteAllLogData]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.DeleteAllLogData
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Deletes all logged table data
-- =============================================
CREATE PROCEDURE DeleteAllLogData


AS
BEGIN

	DELETE FROM Assists
	DELETE FROM CaptureBlocks
	DELETE FROM CaptureList
	DELETE FROM Captures
	DELETE FROM ChatLog
	DELETE FROM Destructions
	DELETE FROM Dominations
	DELETE FROM Engineering
	DELETE FROM Flag
	DELETE FROM Kills
	DELETE FROM LogFiles
	DELETE FROM Matches
	DELETE FROM MatchPlayerList
	DELETE FROM MedicCharge
	DELETE FROM PlayerNameList
	DELETE FROM Players
	DELETE FROM Revenges
	DELETE FROM Rounds
	DELETE FROM Suicides


END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteAllLogDataByLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.DeleteAllLogDataByLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Deletes all logged table data by LogId
-- =============================================
CREATE PROCEDURE DeleteAllLogDataByLogId

@LogId int

AS
BEGIN

	DELETE FROM Assists WHERE LogId = @LogId
	DELETE FROM CaptureBlocks WHERE LogId = @LogId
	DELETE FROM CaptureList WHERE CaptureId IN (SELECT CaptureId FROM Captures WHERE LogId = @LogId)
	DELETE FROM Captures WHERE LogId = @LogId
	DELETE FROM ChatLog WHERE LogId = @LogId
	DELETE FROM Destructions WHERE LogId = @LogId
	DELETE FROM Dominations WHERE LogId = @LogId
	DELETE FROM Engineering WHERE LogId = @LogId
	DELETE FROM Flag WHERE LogId = @LogId
	DELETE FROM Kills WHERE LogId = @LogId
	DELETE FROM LogFiles WHERE LogId = @LogId
	DELETE FROM MatchPlayerList WHERE MatchId IN (SELECT MatchId FROM Matches WHERE LogId = @LogId)
	DELETE FROM Matches WHERE LogId = @LogId
	DELETE FROM MedicCharge WHERE LogId = @LogId
	DELETE FROM Revenges WHERE LogId = @LogId
	DELETE FROM Rounds WHERE LogId = @LogId
	DELETE FROM Suicides WHERE LogId = @LogId


END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DestructionsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.DestructionsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Destructions Table Insert
-- =============================================
CREATE PROCEDURE DestructionsInsert


           @DestructionId int,
           @DestructionTime datetime,
           @Attacker_PlayerId varchar(30),
           @Victim_PlayerId varchar(30),
           @MapId int,
           @RoundId int,
           @MiniRoundId int,
           @WeaponId int,
           @LogId int,
           @DateTime datetime
	

AS
BEGIN

INSERT INTO [Destructions]
           ([DestructionId]
           ,[DestructionTime]
           ,[Attacker_PlayerId]
           ,[Victim_PlayerId]
           ,[MapId]
           ,[RoundId]
           ,[MiniRoundId]
           ,[WeaponId]
           ,[LogId]
           ,[DateTime])
     VALUES
           (@DestructionId,
           @DestructionTime,
           @Attacker_PlayerId,
           @Victim_PlayerId,
           @MapId,
           @RoundId,
           @MiniRoundId,
           @WeaponId,
           @LogId,
           @DateTime)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DestructionsSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.DestructionsSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Destructions Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE DestructionsSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@DestructionCount int = 0 OUTPUT
	
AS

BEGIN


select @DestructionCount = COUNT(DestructionId) FROM Destructions WHERE LogId = @LogId AND Attacker_PlayerId = @PlayerId


END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DominationsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.DominationsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Dominations Table Insert
-- =============================================
CREATE PROCEDURE DominationsInsert


           @DominationId int,
           @DominationTime datetime,
           @Attacker_playerId varchar(30),
           @Victim_playerId varchar(30),
           @RoundId int,
           @MiniRoundId int,
           @MapId int,
           @LogId int,
           @DateTime datetime,
		   @Attacker_classId int,
		   @Victim_classId int
 
AS
BEGIN

INSERT INTO [Dominations]
           ([DominationId]
           ,[DominationTime]
           ,[Attacker_playerId]
           ,[Victim_playerId]
           ,[RoundId]
           ,[MiniRoundId]
           ,[MapId]
           ,[LogId]
           ,[DateTime]
           ,[Attacker_classId]
           ,[Victim_classId] )
     VALUES
           (@DominationId,
           @DominationTime ,
           @Attacker_playerId ,
           @Victim_playerId ,
           @RoundId ,
           @MiniRoundId ,
           @MapId ,
           @LogId ,
           @DateTime,
           @Attacker_classId,
		   @Victim_classId )
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DominationsSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.DominationsSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Dominations Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE DominationsSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@DominationsCount int = 0 OUTPUT
	
AS

BEGIN

select @DominationsCount  = COUNT(DominationId) FROM Dominations
		WHERE LogId = @LogId
		AND Attacker_PlayerId = @PlayerId
		


END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[EngineeringInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.EngineeringInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Engineering Table Insert
-- =============================================
CREATE PROCEDURE EngineeringInsert

           @EngineeringID int,
           @BuiltTime datetime,
           @ObjectId int,
           @RoundId int,
           @MiniRoundId int,
           @MapId int,
           @LogId int,
           @DateTime datetime,
           @Pos_x float,
           @Pos_y float,
           @Pos_z float
	

AS
BEGIN

INSERT INTO [Engineering]
           ([EngineeringID]
           ,[BuiltTime]
           ,[ObjectId]
           ,[RoundId]
           ,[MiniRoundId]
           ,[MapId]
           ,[LogId]
           ,[DateTime]
           ,[Pos_x]
           ,[Pos_y]
           ,[Pos_z])
     VALUES
           (@EngineeringID,
           @BuiltTime,
           @ObjectId,
           @RoundId,
           @MiniRoundId,
           @MapId,
           @LogId,
           @DateTime,
           @Pos_x,
           @Pos_y,
           @Pos_z )
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[EngineeringSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.EngineeringSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/08/08
-- Description:	Engineering Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE EngineeringSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@EngineeringCount int = 0 OUTPUT
	
AS

BEGIN

select @EngineeringCount = COUNT(EngineeringId) FROM Engineering WHERE LogId = @LogId AND PlayerId = @PlayerId


END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FlagEventSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.FlagEventSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	FlagEvent Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE FlagEventSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@FlagEventType varchar(30),
			@FlagEventCount int = 0 OUTPUT
	
AS

BEGIN

select @FlagEventCount = COUNT(FlagId) FROM Flag 
		WHERE LogId = @LogId
		AND PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = @FlagEventType)


END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FlagEventSelectTop5PlayersByEventType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.FlagEventSelectTop5PlayersByEventType
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Flag Event Table Select - Top 5 of a given Flag Event Type
-- =============================================
CREATE PROCEDURE FlagEventSelectTop5PlayersByEventType
	@FlagEventType varchar(30)
AS

BEGIN

DECLARE @FlagEventId int
SELECT @FlagEventId = FlagEventTypeId FROM FlagEventType WHERE FlagEventShortName = @FlagEventType

SELECT TOP 5 PlayerName, COUNT(FlagId) AS [FlagEvents], Flag.PlayerId
FROM Flag INNER JOIN PlayerNameList on Flag.Playerid = PlayerNameList.PlayerId
WHERE FlagEventId = @FlagEventId
GROUP BY PlayerName, Flag.PlayerId
ORDER BY [FlagEvents] DESC

END
GO

--  FlagEventSelectTop5PlayersByEventType 'capture'
--  FlagEventSelectTop5PlayersByEventType 'picked up'
--  FlagEventSelectTop5PlayersByEventType 'defended'
--  FlagEventSelectTop5PlayersByEventType 'dropped'


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FlagEventTypeInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.FlagEventTypeInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/08
-- Description:	FlagEventType Table Insert
-- =============================================
CREATE PROCEDURE FlagEventTypeInsert

           @FlagEventTypeId int
           ,@FlagEventShortName varchar(50)
           ,@FlagEventLongName varchar(50)
AS
BEGIN

INSERT INTO [AnotherSuccessfulProcedure].[dbo].[FlagEventType]
           ([FlagEventTypeId]
           ,[FlagEventShortName]
           ,[FlagEventLongName])
     VALUES
           (@FlagEventTypeId
           ,@FlagEventShortName
           ,@FlagEventLongName)

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FlagInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.FlagInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/08
-- Description:	Flag Table Insert
-- =============================================
CREATE PROCEDURE FlagInsert

           @FlagId int
           ,@PlayerId varchar(30)
           ,@DateTime datetime
           ,@Flag_x float
           ,@Flag_y float
           ,@Flag_z float
           ,@FlagEventId int
           ,@LogId int
           ,@MapId int
           ,@RoundId int
           ,@MiniRoundId int
AS
BEGIN

INSERT INTO [AnotherSuccessfulProcedure].[dbo].[Flag]
           ([FlagId]
           ,[PlayerId]
           ,[DateTime]
           ,[Flag_x]
           ,[Flag_y]
           ,[Flag_z]
           ,[FlagEventId]
           ,[LogId]
           ,[MapId]
           ,[RoundId]
           ,[MiniRoundId])
     VALUES
           (@FlagId
           ,@PlayerId
           ,@DateTime
           ,@Flag_x
           ,@Flag_y
           ,@Flag_z
           ,@FlagEventId
           ,@LogId
           ,@MapId
           ,@RoundId
           ,@MiniRoundId)

END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[KillFormatted]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.KillFormatted
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	KillFormatted Select
-- =============================================
CREATE PROCEDURE KillFormatted

		   @KillId int
AS
BEGIN

DECLARE @AttackerWeaponName varchar(30),
		@AttackerClass varchar(30),
		@VictimClass varchar(30),
		@AttackerTeam varchar(30),
		@VictimTeam varchar(30)
		
		
		SET @AttackerWeaponName = (SELECT TOP 1 (WeaponFullName) FROM Weapons WHERE WeaponId = (SELECT Attacker_weaponId from Kills where KillId = @KillId))
		SET @AttackerClass = (SELECT TOP 1 (ClassLongName) FROM Classes WHERE ClassId = (SELECT Attacker_ClassId from Kills where KillId = @KillId))
		SET @VictimClass = (SELECT TOP 1 (ClassLongName) FROM Classes WHERE ClassId = (SELECT Victim_ClassId from Kills where KillId = @KillId))
		SET @AttackerTeam = (SELECT TOP 1 (TeamFullname) FROM Teams WHERE TeamId = (SELECT Attacker_Team from Kills where KillId = @KillId))
		SET @VictimTeam = (SELECT TOP 1 (TeamFullname) FROM Teams WHERE TeamId = (SELECT Victim_Team from Kills where KillId = @KillId))
		


SELECT KillId, KillTime, Attacker_playerId, @AttackerWeaponName as [Weapon Name], 
		@AttackerClass as [Attacker Class], @AttackerTeam as [Attacker Team], 
		Victim_playerId, @VictimClass as [Victim Class], @VictimTeam as [Victim Team]
FROM Kills
WHERE KillId = @KillId		

END
GO

 

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[KillsCountSelect]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.KillsCountSelect
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/24/08
-- Description:	Kills Count Select
-- =============================================
CREATE PROCEDURE KillsCountSelect

AS

BEGIN

	SELECT COUNT(Kills.KillId) AS [Kills] FROM Kills
	
END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[KillsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.KillsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Kills Table Insert
-- =============================================
CREATE PROCEDURE KillsInsert

		   @KillId int, 
           @RoundId int, 
           @MiniRoundId int, 
           @KillTime datetime, 
           @Attacker_playerId varchar(30), 
           @Attacker_weaponId int, 
           @Attacker_classId int, 
           @Attacker_pos_x float, 
           @Attacker_pos_y float, 
           @Attacker_pos_z float, 
           @Attacker_team int, 
           @Victim_playerId varchar(30), 
           @Victim_pos_x float, 
           @Victim_pos_y float, 
           @Victim_pos_z float, 
           @Victim_team int, 
           @Victim_classId int,
           @MapId int, 
           @CustomKillId int, 
           @LogId int, 
           @DateTime datetime
	

AS
BEGIN

INSERT INTO [Kills]
           ([KillId]
           ,[RoundId]
           ,[MiniRoundId]
           ,[KillTime]
           ,[Attacker_playerId]
           ,[Attacker_weaponId]
           ,[Attacker_classId]
           ,[Attacker_pos_x]
           ,[Attacker_pos_y]
           ,[Attacker_pos_z]
           ,[Attacker_team]
           ,[Victim_playerId]
           ,[Victim_pos_x]
           ,[Victim_pos_y]
           ,[Victim_pos_z]
           ,[Victim_team]
           ,[Victim_classId]
           ,[MapId]
           ,[CustomKillId]
           ,[LogId]
           ,[DateTime])
     VALUES
           (@KillId, 
           @RoundId, 
           @MiniRoundId, 
           @KillTime, 
           @Attacker_playerId, 
           @Attacker_weaponId, 
           @Attacker_classId, 
           @Attacker_pos_x, 
           @Attacker_pos_y, 
           @Attacker_pos_z, 
           @Attacker_team, 
           @Victim_playerId, 
           @Victim_pos_x, 
           @Victim_pos_y, 
           @Victim_pos_z, 
           @Victim_team, 
           @Victim_classId,
           @MapId, 
           @CustomKillId, 
           @LogId, 
           @DateTime)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[KillsSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.KillsSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Kills Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE KillsSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@KillCount int = 0 OUTPUT
	
AS

BEGIN

select @KillCount = COUNT(KillId) FROM Kills WHERE LogId = @LogId AND Attacker_PlayerId = @PlayerId


END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[LeagueDeleteLeagueId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.LeagueDeleteLeagueId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/03/08
-- Description:	LeagueDeleteLeagueId Select
-- =============================================
CREATE PROCEDURE LeagueDeleteLeagueId

	@LeagueId int
		   
AS
BEGIN

DELETE FROM Leagues
WHERE LeagueId = @LeagueId

END
GO

--    LeagueSelectByLeagueId 1


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[LeagueSelect]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.LeagueSelect
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/03/08
-- Description:	League Select
-- =============================================
CREATE PROCEDURE LeagueSelectByLeagueId

	@LeagueId int
		   
AS
BEGIN

SELECT LeagueId, LeagueName FROM Leagues
ORDER BY LeagueId DESC

END
GO

--    LeagueSelect


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[LeagueSelectByLeagueId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.LeagueSelectByLeagueId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/03/08
-- Description:	LeagueSelectByLeagueId Select
-- =============================================
CREATE PROCEDURE LeagueSelectByLeagueId

	@LeagueId int
		   
AS
BEGIN

SELECT LeagueId, LeagueName FROM Leagues
WHERE LeagueId = @LeagueId
ORDER BY LeagueId DESC

END
GO

--    LeagueSelectByLeagueId 1

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[LogFilesInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.LogFilesInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	LogFiles Table Insert
-- =============================================
CREATE PROCEDURE LogFilesInsert

           @LogId int,
           @LogName varchar(100),
           @LogFileName varchar(100),
           @LogLocation varchar(100),
           @DateTime datetime,
           @MapId int,
           @RedFinalScore int,
           @BlueFinalScore int,
           @MatchStartTime datetime,
           @MatchEndTime datetime,
           @TotalMatchLength int,
           @LeagueId int
	

AS
BEGIN

INSERT INTO [LogFiles]
           ([LogId]
           ,[LogName]
           ,[LogFileName]
           ,[LogLocation]
           ,[DateTime]
           ,[MapId]
           ,[RedFinalScore]
           ,[BlueFinalScore]
           ,[MatchStartTime]
           ,[MatchEndTime]
           ,[TotalMatchLength]
           ,[LeagueId] )
     VALUES
           (@LogId,
           @LogName,
           @LogFileName, 
           @LogLocation,
           @DateTime,
           @MapId,
           @RedFinalScore,
           @BlueFinalScore,
           @MatchStartTime,
           @MatchEndTime, 
           @TotalMatchLength,
           @LeagueId )
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[LogFilesSelectById]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.LogFilesSelectById
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/2008
-- Description:	Gets a log file record by id
-- =============================================
CREATE PROCEDURE LogFilesSelectById 

	@LogId int

AS
BEGIN

	SET NOCOUNT ON;

	SELECT [LogId]
		  ,[LogName]
		  ,[LogFileName]
		  ,[LogLocation]
		  ,[DateTime]
		  ,[MapId]
		  ,[RedFinalScore]
		  ,[BlueFinalScore]
		  ,[MatchStartTime]
		  ,[MatchEndTime]
		  ,[TotalMatchLength]
		  ,[LeagueId]

	FROM
		[AnotherSuccessfulProcedure].[dbo].[LogFiles]
	WHERE
		[LogId] = @LogId
END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[LogFilesSelectByLeagueId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.LogFilesSelectByLeagueId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/03/2008
-- Description:	Gets a log file record by League id
-- =============================================
CREATE PROCEDURE LogFilesSelectByLeagueId 

	@LeagueId int

AS
BEGIN

	SET NOCOUNT ON;

	SELECT [LogId]
		  ,[LogName]
		  ,[LogFileName]
		  ,[LogLocation]
		  ,[DateTime]
		  ,[MapId]
		  ,[RedFinalScore]
		  ,[BlueFinalScore]
		  ,[MatchStartTime]
		  ,[MatchEndTime]
		  ,[TotalMatchLength]
		  ,[LeagueId]

	FROM
		[AnotherSuccessfulProcedure].[dbo].[LogFiles]
	WHERE
		[LeagueId] = @LeagueId
END
GO

--   LogFilesSelectByLeagueId 2



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[LogFilesSelectByName]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.LogFilesSelectByName
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/2008
-- Description:	Gets a log file record by id
-- =============================================
CREATE PROCEDURE LogFilesSelectByName 

	@LogName varchar(100)

AS
BEGIN

	SET NOCOUNT ON;

	SELECT [LogId]
		  ,[LogName]
		  ,[LogFileName]
		  ,[LogLocation]
		  ,[DateTime]
		  ,[MapId]
		  ,[RedFinalScore]
		  ,[BlueFinalScore]
		  ,[MatchStartTime]
		  ,[MatchEndTime]
		  ,[TotalMatchLength]
		  ,[LeagueId]
			
	FROM
		[AnotherSuccessfulProcedure].[dbo].[LogFiles]
	WHERE
		[LogName] = @LogName
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MapsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MapsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Maps Table Insert
-- =============================================
CREATE PROCEDURE MapsInsert

            @MapId int,
            @MapShortName varchar(30),
            @MapFullName varchar(50),
            @MapTypeId varchar(50)
	

AS
BEGIN

INSERT INTO [Maps]
           ([MapId]
           ,[MapShortName]
           ,[MapFullName]
           ,[MapTypeId])
     VALUES
           (@MapId,
            @MapShortName,
            @MapFullName,
            @MapTypeId)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MapsSelectMapByName]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MapsSelectMapByName
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Maps Select By Name
-- =============================================
CREATE PROCEDURE MapsSelectMapByName

            
            @MapShortName varchar(30)
	

AS
BEGIN

SELECT [MapId], [MapShortName], [MapFullName], [MapTypeId]
FROM [Maps]
WHERE [MapShortName] = @MapShortName

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MapTypeInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MapTypeInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	MapType Table Insert
-- =============================================
CREATE PROCEDURE MapTypeInsert

            @MapTypeId int,
            @MapTypeName varchar(30)            
	
AS
BEGIN

INSERT INTO [MapType]
           ([MapTypeId]
           ,[MapTypeName])
     VALUES
           (@MapTypeId,
            @MapTypeName)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MatchesByLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MatchesByLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/2008
-- Description:	Gets a Matches record by id
-- =============================================
CREATE PROCEDURE MatchesByLogId 

	@LogId int

AS
BEGIN

	SET NOCOUNT ON;

SELECT [MatchId]
      ,[RedFinalScore]
      ,[BlueFinalScore]
      ,[MatchStartTime]
      ,[MatchEndTime]
      ,[LogId]
      ,[MapId]
FROM 
	[AnotherSuccessfulProcedure].[dbo].[Matches]
WHERE
		[LogId] = @LogId
END
GO

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MatchesInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MatchesInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/08
-- Description:	Matches Table Insert
-- =============================================
CREATE PROCEDURE MatchesInsert

           @MatchId int
           ,@RedFinalScore int
           ,@BlueFinalScore int
           ,@MatchStartTime datetime
           ,@MatchEndTime datetime
           ,@LogId int
           ,@MapId int
AS
BEGIN

INSERT INTO [AnotherSuccessfulProcedure].[dbo].[Matches]
           ([MatchId]
           ,[RedFinalScore]
           ,[BlueFinalScore]
           ,[MatchStartTime]
           ,[MatchEndTime]
           ,[LogId]
           ,[MapId])
     VALUES
           (@MatchId
           ,@RedFinalScore
           ,@BlueFinalScore
           ,@MatchStartTime
           ,@MatchEndTime
           ,@LogId
           ,@MapId)

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MatchPlayerListByMatchId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MatchPlayerListByMatchId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/2008
-- Description:	Gets a MatchesPlayerList series of records by id
-- =============================================
CREATE PROCEDURE MatchPlayerListByMatchId 

	@MatchId int

AS
BEGIN

	SET NOCOUNT ON;

SELECT [MatchPlayerListId]
      ,[PlayerId]
      ,[TeamId]
	  ,[MatchId]
FROM [AnotherSuccessfulProcedure].[dbo].[MatchPlayerList]

WHERE
	[MatchId] = @MatchId
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MatchPlayerListByMatchIdTeamId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MatchPlayerListByMatchIdTeamId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/2008
-- Description:	Gets a MatchesPlayerList series of records by MatchId and TeamId
-- =============================================
CREATE PROCEDURE MatchPlayerListByMatchIdTeamId

	@MatchId int,
	@TeamId int

AS
BEGIN

	SET NOCOUNT ON;

SELECT [MatchPlayerListId]
      ,[PlayerId]
      ,[TeamId]
	  ,[MatchId]
FROM [AnotherSuccessfulProcedure].[dbo].[MatchPlayerList]

WHERE
	[MatchId] = @MatchId
AND
	[TeamId] = @TeamId
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MatchPlayerListInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MatchPlayerListInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/05/08
-- Description:	MatchPlayerList Table Insert
-- =============================================
CREATE PROCEDURE MatchPlayerListInsert

           @MatchPlayerListId int
           ,@MatchId int
           ,@PlayerId varchar(30)
           ,@TeamId int
AS
BEGIN

INSERT INTO [AnotherSuccessfulProcedure].[dbo].[MatchPlayerList]
           ([MatchPlayerListId]
           ,[MatchId]
           ,[PlayerId]
           ,[TeamId])
     VALUES
           (@MatchPlayerListId
           ,@MatchId
           ,@PlayerId
           ,@TeamId)

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MedicChargeInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MedicChargeInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	MedicCharge Table Insert
-- =============================================
CREATE PROCEDURE MedicChargeInsert

           @ChargeId int,
           @ChargeDeployTime datetime,
           @PlayerId varchar(30),
           @RoundId int,
           @MiniRoundId int,
           @MapId int,
           @LogId int,
           @DateTime datetime,
           @ChargeType varchar(30)
	

AS
BEGIN

INSERT INTO [MedicCharge]
           ([ChargeId]
           ,[ChargeDeployTime]
           ,[PlayerId]
           ,[RoundId]
           ,[MiniRoundId]
           ,[MapId]
           ,[LogId]
           ,[DateTime]
           ,[ChargeType])
     VALUES
           (@ChargeId,
           @ChargeDeployTime,
           @PlayerId,
           @RoundId,
           @MiniRoundId,
           @MapId,
           @LogId,
           @DateTime,
           @ChargeType)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MedicChargeSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MedicChargeSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Maps Select By Name
-- =============================================
CREATE PROCEDURE MedicChargeSelectCountByPlayerIdLogId

            
            @LogId int,
			@PlayerId varchar(30),
			@ChargeCount int = 0 OUTPUT
	
AS

BEGIN

select @ChargeCount  = COUNT(ChargeId) FROM MedicCharge
		WHERE LogId = @LogId
		AND PlayerId = @PlayerId
		

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[MedicChargeSelectTop5PlayersByChargeType]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.MedicChargeSelectTop5PlayersByChargeType
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Medic Charge Table Select - Top 5 of a given Charge Type
-- =============================================
CREATE PROCEDURE MedicChargeSelectTop5PlayersByChargeType
	@ChargeType varchar(30)
AS

BEGIN

SELECT TOP 5 PlayerName, COUNT(ChargeId) AS [Charges], MedicCharge.PlayerId
FROM MedicCharge INNER JOIN PlayerNameList on MedicCharge.Playerid = PlayerNameList.PlayerId
WHERE ChargeType = @ChargeType
GROUP BY PlayerName, MedicCharge.PlayerId
ORDER BY [Charges] DESC

END
GO

--  MedicChargeSelectTop5PlayersByChargeType 'chargedeployed'


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[ObjectsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.ObjectsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Objects Table Insert
-- =============================================
CREATE PROCEDURE ObjectsInsert

            @ObjectId int,
            @ObjectShortName varchar(50),
            @ObjectLongName varchar(100)
	

AS
BEGIN

INSERT INTO [Objects]
           ([ObjectId]
           ,[ObjectShortName]
           ,[ObjectLongName])
     VALUES
           (@ObjectId,
            @ObjectShortName,
            @ObjectLongName)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerClassStatsSelectByPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerClassStatsSelectByPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/10/08
-- Description:	PlayerClassStats Table Select By PlayerId
-- =============================================
CREATE PROCEDURE PlayerClassStatsSelectByPlayerId

			@PlayerId varchar(30),
			@ClassId int
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Points varchar(8),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio varchar(8),
		@Dominations int,
		@Ubers int,
		@Healing float,
		@TimesPlayed int
		
		

select @Assists = COUNT(AssistId) FROM Assists WHERE assister_PlayerId = @PlayerId and ClassId = @ClassId

SELECT @ReturnName = ClassLongName From classes
WHERE ClassId = @ClassId




select @Kills = COUNT(KillId) FROM Kills WHERE Attacker_PlayerId = @PlayerId  and Attacker_ClassId = @ClassId

select @Deaths = COUNT(KillId) FROM Kills WHERE Victim_PlayerId = @PlayerId
	AND Victim_classId = @ClassId
	
select @Ubers  = COUNT(ChargeId) FROM MedicCharge
		WHERE PlayerId = @PlayerId


select @Dominations = Count(DominationId) FROM Dominations
WHERE Attacker_playerId = @PlayerId


SET @Healing = 0.0

SET @TimesPlayed = 0

select @ReturnName as [ClassName], 0 as [Points], 
		@TimesPlayed as [TimesPlayed],
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Dominations as [Dominations], @Ubers as [Ubers],
		@PlayerId as [PlayerId], @ClassId as [ClassId]

END
GO

--    PlayerClassStatsSelectByPlayerId 'STEAM_0:0:13191839', 7





USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerClassStatsSelectByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerClassStatsSelectByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/03/08
-- Description:	PlayerClassStats Table Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE PlayerClassStatsSelectByPlayerIdLogId

			@PlayerId varchar(30),
			@ClassId int,
			@LogId varchar(8000)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Points varchar(8),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio varchar(8),
		@Dominations int,
		@Ubers int,
		@Healing float,
		@TimesPlayed int
		
		


SELECT @ReturnName = ClassLongName From classes
WHERE ClassId = @ClassId



DECLARE @SQL varchar(600)


DECLARE @ResultSet TABLE (resultCount int)

-- KILLS
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND Attacker_ClassId = ' + CAST(@ClassId AS varchar(2)) + '
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Kills = resultCount FROM @ResultSet

-- ASSISTS
SET @SQL = 'SELECT COUNT(AssistId) FROM Assists WHERE assister_PlayerId = ''' + @PlayerId + '''
				AND ClassId = ' + CAST(@ClassId AS varchar(2)) + '
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Assists= resultCount FROM @ResultSet

-- DEATHS
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Victim_PlayerId = ''' + @PlayerId + '''
				AND Victim_classId = ' + CAST(@ClassId AS varchar(2)) + '
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Deaths= resultCount FROM @ResultSet


-- UBERS 
	
SET @SQL = 'SELECT COUNT(ChargeId) FROM MedicCharge WHERE PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Ubers= resultCount FROM @ResultSet

-- DOMINATIONS

SET @SQL = 'SELECT Count(DominationId) FROM Dominations WHERE Attacker_playerId = ''' + @PlayerId + '''
				AND Attacker_classId = ' + CAST(@ClassId AS varchar(2)) + '
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Dominations = resultCount FROM @ResultSet


SET @Healing = 0.0

SET @TimesPlayed = 0

select @ReturnName as [ClassName], 0 as [Points], 
		@TimesPlayed as [TimesPlayed],
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Dominations as [Dominations], @Ubers as [Ubers],
		@PlayerId as [PlayerId], @ClassId as [ClassId]

END
GO

--    PlayerClassStatsSelectByPlayerIdLogId 'STEAM_0:0:13191839', 7, '1,3'





USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerNameListInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerNameListInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	PlayerNameList Table Insert
-- =============================================
CREATE PROCEDURE PlayerNameListInsert

            @PlayerNameListId int,
            @PlayerId varchar(30),
            @PlayerName varchar(50)
	

AS
BEGIN

INSERT INTO [PlayerNameList]
           ([PlayerNameListId]
           ,[PlayerId]
           ,[PlayerName])
     VALUES
           (@PlayerNameListId,
            @PlayerId,
            @PlayerName)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerNameListSelectById]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerNameListSelectById
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	PlayersNameList Table Select By Id
-- =============================================
CREATE PROCEDURE PlayerNameListSelectById

            @PlayerId varchar(30)
	
AS
BEGIN

SELECT * FROM PlayerNameList Where PlayerId = @PlayerId

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerNameListSelectByName]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerNameListSelectByName
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	PlayersNameList Table Select By Name
-- =============================================
CREATE PROCEDURE PlayerNameListSelectByName

            @PlayerName varchar(50)
	
AS
BEGIN

SELECT PlayerNameListId, PlayerId, PlayerName FROM PlayerNameList Where PlayerName = @PlayerName

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerNameSingleNameSelectByPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerNameSingleNameSelectByPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	PlayerStatsTeam Table Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE PlayerNameSingleNameSelectByPlayerId

            @PlayerId varchar(30),
			@ReturnName varchar(30) = null OUTPUT
	
AS

BEGIN



SELECT @ReturnName = PlayerName FROM 

(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID FROM PlayerNameList
GROUP BY PlayerName, PlayerId ) as NewTable

WHERE PlayerId = @PlayerID 
GROUP BY PlayerName



END
GO

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerShortStatsSelectByPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerShortStatsSelectByPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/10/08
-- Description:	PlayerShortStats Table Select By PlayerId
-- =============================================
CREATE PROCEDURE PlayerShortStatsSelectByPlayerId

			@PlayerId varchar(30)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Points varchar(8),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio varchar(8),
		@Ubers int,
		@Healing float,
		@Captures int,
		@FlagsCaptured int,
		@Dominations int
		
		

select @Assists = COUNT(AssistId) FROM Assists WHERE assister_PlayerId = @PlayerId

-- Player Name
SELECT @ReturnName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @PlayerID 
	GROUP BY PlayerName


select @Kills = COUNT(KillId) FROM Kills WHERE Attacker_PlayerId = @PlayerId

select @Deaths = COUNT(KillId) FROM Kills WHERE Victim_PlayerId = @PlayerId
	AND Victim_classId != (select top 1 ClassId from classes WHERE ClassShortName = 'medic')

select @Captures = COUNT(CaptureListId) FROM CaptureList 
		WHERE PlayerId = @PlayerId

select @Dominations  = COUNT(DominationId) FROM Dominations
		WHERE Attacker_PlayerId = @PlayerId

select @FlagsCaptured = COUNT(FlagId) FROM Flag 
		WHERE PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = 'captured')

select @Ubers  = COUNT(ChargeId) FROM MedicCharge
		WHERE PlayerId = @PlayerId


SET @Healing = 0.0


select @ReturnName as [PlayerName], 0 as [Points], 
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Dominations as [Dominations], @Ubers as [Ubers],
		@Captures as [Captures], @FlagsCaptured as [FlagsCaptured],
		@PlayerId as [PlayerId]

END
GO

--    PlayerShortStatsSelectByPlayerId 'STEAM_0:0:13191839'





USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerShortStatsSelectByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerShortStatsSelectByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/10/08
-- Description:	PlayerShortStats Table Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE PlayerShortStatsSelectByPlayerIdLogId

			@PlayerId varchar(30),
			@LogId varchar(8000)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Points varchar(8),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio varchar(8),
		@Ubers int,
		@Healing float,
		@Captures int,
		@FlagsCaptured int,
		@Dominations int
		
DECLARE @SQL varchar(600)
DECLARE @ResultSet TABLE (resultCount int)


SET @SQL = 'SELECT COUNT(AssistId) FROM Assists WHERE assister_PlayerId = ''' + @PlayerId + '''
	AND LogId in (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)

SELECT @Assists = resultCount FROM @ResultSet	


-- Player Name
SELECT @ReturnName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @PlayerID 
	GROUP BY PlayerName


-- KILLS
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Attacker_PlayerId = ''' + @PlayerId + '''
			AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Kills = resultCount FROM @ResultSet


-- Deaths
SET @SQL = 'SELECT COUNT(KillId) FROM Kills WHERE Victim_PlayerId = ''' + @PlayerId + '''
	AND Victim_classId != (select top 1 ClassId from classes WHERE ClassShortName = ''medic'')
	AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Deaths = resultCount FROM @ResultSet

-- Captures
SET @SQL = 'SELECT COUNT(CaptureListId) FROM CaptureList, Captures 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND Captures.CaptureId = CaptureList.CaptureId
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Captures = resultCount FROM @ResultSet


-- Dominations

SET @SQL = 'SELECT COUNT(DominationId) FROM Dominations
				WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Dominations = resultCount FROM @ResultSet


-- FlagsCaptured

SET @SQL = 'SELECT COUNT(FlagId) FROM Flag 
				WHERE PlayerId = ''' + @PlayerId + '''
				AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = ''captured'')
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @FlagsCaptured = resultCount FROM @ResultSet


-- Ubers

SET @SQL = 'SELECT COUNT(ChargeId) FROM MedicCharge
				WHERE PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Ubers = resultCount FROM @ResultSet


SET @Healing = 0.0


select @ReturnName as [PlayerName], 0 as [Points], 
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Dominations as [Dominations], @Ubers as [Ubers],
		@Captures as [Captures], @FlagsCaptured as [FlagsCaptured],
		@PlayerId as [PlayerId]

END
GO

--      PlayerShortStatsSelectByPlayerIdLogId 'STEAM_0:1:100283', '1'




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayersInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayersInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Players Table Insert
-- =============================================
CREATE PROCEDURE PlayersInsert

            @PlayerId varchar(30),
            @FirstSeen datetime,
            @LastSeen datetime
	

AS
BEGIN

INSERT INTO [Players]
           ([PlayerId]
           ,[FirstSeen]
           ,[LastSeen])
     VALUES
           (@PlayerId,
            @FirstSeen,
            @LastSeen)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayersSelectById]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayersSelectById
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Players Table Select By Id
-- =============================================
CREATE PROCEDURE PlayersSelectById

            @PlayerId varchar(30)
	
AS
BEGIN

SELECT * FROM Players Where PlayerId = @PlayerId

END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayersSelectPlayerKillersTop5ByVictimPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayersSelectPlayerKillersTop5ByVictimPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Players Table Select - Top 5 Killers of a supplied Victim Player Id
-- =============================================
CREATE PROCEDURE PlayersSelectPlayerKillersTop5ByVictimPlayerId

            @Victim_PlayerId varchar(30)
	
AS

BEGIN

SELECT TOP 5 PlayerName, count(killid) as [Kills], PlayerId FROM Kills
INNER JOIN PlayerNameList ON Attacker_playerid = PlayerNameList.PlayerID
 where 
Victim_PlayerId = @Victim_PlayerId
group by Attacker_PlayerId, PlayerName, PlayerId
order by [Kills] DESC

END
GO



--  PlayersSelectPlayerKillersTop5ByVictimPlayerId 'STEAM_0:1:17834846'


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayersSelectTop5Deaths]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayersSelectTop5Deaths
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Players Table Select - Top 5 Players that Died
-- =============================================
CREATE PROCEDURE PlayersSelectTop5Deaths
	
AS

BEGIN

SELECT  TOP 5  PlayerName, count(Victim_Playerid) as [Deaths], PlayerId FROM Kills
INNER JOIN PlayerNameList ON Victim_Playerid = PlayerNameList.PlayerID
group by Victim_Playerid, PlayerName, PlayerId
order by [Deaths] DESC

END
GO

--  PlayersSelectTop5Deaths 


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayersSelectTop5Dominated]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayersSelectTop5Dominated
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Players Table Select - Top 5 Players that were dominated
-- =============================================
CREATE PROCEDURE PlayersSelectTop5Dominated
	
AS

BEGIN

SELECT  TOP 5  PlayerName, count(Victim_Playerid) as [Count], PlayerId FROM Dominations
INNER JOIN PlayerNameList ON Victim_Playerid = PlayerNameList.PlayerID
group by Victim_Playerid, PlayerName, PlayerId
order by [Count] DESC

END
GO

--  PlayersSelectTop5Dominated 


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayersSelectTop5Dominators]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayersSelectTop5Dominators
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Players Table Select - Top 5 Dominating Players
-- =============================================
CREATE PROCEDURE PlayersSelectTop5Dominators
	
AS

BEGIN

SELECT  TOP 5  PlayerName, count(Attacker_Playerid) as [Count], PlayerId FROM Dominations
INNER JOIN PlayerNameList ON Attacker_Playerid = PlayerNameList.PlayerID
group by Attacker_Playerid, PlayerName, PlayerId
order by [Count] DESC

END
GO

--  PlayersSelectTop5Dominators 


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayersSelectTop5Revenges]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayersSelectTop5Revenges
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Players Table Select - Top 5 Revenging Players
-- =============================================
CREATE PROCEDURE PlayersSelectTop5Revenges
	
AS

BEGIN

SELECT  TOP 5  PlayerName, count(Attacker_Playerid) as [Count], PlayerId FROM Revenges
INNER JOIN PlayerNameList ON Attacker_Playerid = PlayerNameList.PlayerID
group by Attacker_Playerid, PlayerName, PlayerId
order by [Count] DESC

END
GO

--  PlayersSelectTop5Revenges

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerStatsSelectByPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerStatsSelectByPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	PlayerStats Table Select By PlayerId
-- =============================================
CREATE PROCEDURE PlayerStatsSelectByPlayerId

			@PlayerId varchar(30)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio float,
		@Destructions int,
		@BuiltObjects int,
		@Dominations int,
		@Revenges int,
		@Ubers int,
		@Backstabs int,
		@Headshots int,
		@Captures int,
		@CaptureBlocks int,
		@FlagsCaptured int,
		@FlagsTouched int,
		@FlagsDefended int
		

select @Assists = COUNT(AssistId) FROM Assists WHERE assister_PlayerId = @PlayerId

-- Player Name
SELECT @ReturnName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @PlayerID 
	GROUP BY PlayerName


select @Kills = COUNT(KillId) FROM Kills WHERE Attacker_PlayerId = @PlayerId

select @Deaths = COUNT(KillId) FROM Kills WHERE Victim_PlayerId = @PlayerId
	AND Victim_classId != (select top 1 ClassId from classes WHERE ClassShortName = 'medic')

select @Destructions = COUNT(DestructionId) FROM Destructions WHERE Attacker_PlayerId = @PlayerId
select @BuiltObjects = COUNT(EngineeringId) FROM Engineering WHERE PlayerId = @PlayerId
select @Captures = COUNT(CaptureListId) FROM CaptureList 
		WHERE PlayerId = @PlayerId


select @CaptureBlocks = COUNT(CaptureBlockId) FROM CaptureBlocks 
		WHERE PlayerId = @PlayerId

select @FlagsCaptured = COUNT(FlagId) FROM Flag 
		WHERE PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = 'captured')

select @FlagsTouched = COUNT(FlagId) FROM Flag 
		WHERE PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = 'picked up')

select @FlagsDefended = COUNT(FlagId) FROM Flag 
		WHERE PlayerId = @PlayerId
		AND FlagEventId = (SELECT FlagEventId FROM FlagEventType WHERE FlagEventShortName = 'defended')

select @Ubers  = COUNT(ChargeId) FROM MedicCharge
		WHERE PlayerId = @PlayerId

select @Dominations  = COUNT(DominationId) FROM Dominations
		WHERE Attacker_PlayerId = @PlayerId

select @Revenges  = COUNT(RevengeId) FROM Revenges
		WHERE Attacker_PlayerId = @PlayerId

select @Backstabs = COUNT(KillId) FROM Kills
		WHERE Attacker_PlayerId = @PlayerId
		AND CustomKillId = (SELECT CustomKillId FROM CustomKills WHERE CustomKillShortName = 'backstab')

select @Headshots = COUNT(KillId) FROM Kills
		WHERE Attacker_PlayerId = @PlayerId
		AND CustomKillId = (SELECT CustomKillId FROM CustomKills WHERE CustomKillShortName = 'headshot')


select @ReturnName as [PlayerName], 0 as [Points], 
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Destructions as [Destructions], @BuiltObjects as [BuiltObjects], 
		@Dominations as [Dominations], @Revenges as [Revenges],
		@Ubers as [Ubers], @Backstabs as [Backstabs], @Headshots as [Headshots],
		@Captures as [Captures], @CaptureBlocks as [CaptureBlocks], 
		@FlagsCaptured as [FlagsCaptured], @FlagsTouched as [FlagsTouched],
		@FlagsDefended as [FlagsDefended], 
		@PlayerId as [PlayerId]

END
GO

--PlayerStatsSelectByPlayerId 'STEAM_0:0:13191839'


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerWeaponStatsSelectByPlayerIdLogIdList]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerWeaponStatsSelectByPlayerIdLogIdList
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Player Victims Select By PlayerId and LogIdList
-- =============================================
CREATE PROCEDURE PlayerWeaponStatsSelectByPlayerIdLogIdList

			@Attacker_playerId varchar(30),
			@WeaponId varchar(2),
			@LogId varchar(8000)
	
AS
BEGIN


DECLARE @WeaponName varchar(50),
		@Kills int,
		@Deaths int,
		@KillDeathRatio varchar(8)

DECLARE @SQL varchar(600)


DECLARE @ResultSet TABLE (resultCount int)

-- KILLS
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Attacker_PlayerId = ''' + @Attacker_playerId + '''
				AND Attacker_weaponId = ' + @WeaponId + '
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Kills = resultCount FROM @ResultSet

-- Deaths
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Victim_PlayerId = ''' + @Attacker_playerId + '''
				AND Attacker_weaponId = ' + @WeaponId + '
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
select @Deaths = resultCount FROM @ResultSet

SELECT @WeaponName = WeaponFullName from [Weapons] WHERE WeaponId = @WeaponId
SET @KillDeathRatio = ''


SELECT @WeaponName AS [Weapon], @Kills AS [Kills], @Deaths AS [Deaths], 
@KillDeathRatio AS [KillDeathRatio], @WeaponId as [WeaponId]

END
GO

--  PlayerWeaponStatsSelectByPlayerIdLogIdList 'STEAM_0:1:6788967',4, '1'

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerStatsTeamSelectByLogIdPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerStatsTeamSelectByLogIdPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	PlayerStatsTeam Table Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE PlayerStatsTeamSelectByLogIdPlayerId

            @LogId int,
			@PlayerId varchar(30)
	
AS
BEGIN

DECLARE @Assists int,
		@ReturnName varchar(50),
		@Kills int,
		@Deaths int,
		@Destructions int,
		@BuiltObjects int,
		@Captures int,
		@CaptureBlocks int,
		@FlagsCaptured int,
		@FlagsTouched int,
		@FlagsDefended int,
		@Ubers int,
		@Dominations int,
		@Revenges int,
		@KillDeathRatio float,
		@Backstabs int,
		@Headshots int


EXECUTE AssistsSelectCountByPlayerIdLogId @LogId, @PlayerId, @Assists OUTPUT
EXECUTE PlayerNameSingleNameSelectByPlayerId @PlayerId, @ReturnName OUTPUT
EXECUTE KillsSelectCountByPlayerIdLogId @LogId, @PlayerId, @Kills OUTPUT
EXECUTE DeathsSelectCountByPlayerIdLogId @LogId, @PlayerId, @Deaths OUTPUT
EXECUTE DestructionsSelectCountByPlayerIdLogId @LogId, @PlayerId, @Destructions OUTPUT
EXECUTE EngineeringSelectCountByPlayerIdLogId @LogId, @PlayerId, @BuiltObjects OUTPUT
EXECUTE CaptureListSelectCountByPlayerIdLogId @LogId, @PlayerId, @Captures OUTPUT
EXECUTE CaptureBlocksSelectCountByPlayerIdLogId @LogId, @PlayerId, @CaptureBlocks OUTPUT
EXECUTE FlagEventSelectCountByPlayerIdLogId @LogId, @PlayerId, 'captured', @FlagsCaptured OUTPUT
EXECUTE FlagEventSelectCountByPlayerIdLogId @LogId, @PlayerId, 'picked up', @FlagsTouched OUTPUT
EXECUTE FlagEventSelectCountByPlayerIdLogId @LogId, @PlayerId, 'defended', @FlagsDefended OUTPUT
EXECUTE MedicChargeSelectCountByPlayerIdLogId @LogId, @PlayerId, @Ubers OUTPUT
EXECUTE DominationsSelectCountByPlayerIdLogId @LogId, @PlayerId, @Dominations OUTPUT
EXECUTE RevengesSelectCountByPlayerIdLogId @LogId, @PlayerId, @Revenges OUTPUT
EXECUTE CustomKillSelectCountByPlayerIdLogId @LogId, @PlayerId, 'backstab', @Backstabs OUTPUT
EXECUTE CustomKillSelectCountByPlayerIdLogId @LogId, @PlayerId, 'headshot', @Headshots OUTPUT



select @ReturnName as [PlayerName], 0 as [Points], @Kills as [Kills], @Assists as [Assists], @Deaths as [Deaths],
		@Destructions as [Destructions], @BuiltObjects as [BuiltObjects], @Captures as [Captures],
		@CaptureBlocks as [CaptureBlocks], @FlagsCaptured as [FlagsCaptured], @FlagsTouched as [FlagsTouched],
		@FlagsDefended as [FlagsDefended], @Ubers as [Ubers], @Dominations as [Dominations], @Revenges as [Revenges],
		0.0 as [KillDeathRatio], @Backstabs as [Backstabs], @Headshots as [Headshots], @PlayerId as [PlayerId]

END
GO

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayersUpdate]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayersUpdate
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Players Table Update
-- =============================================
CREATE PROCEDURE PlayersUpdate

            @PlayerId varchar(30),
            @LastSeen datetime
	

AS
BEGIN

UPDATE [Players]
SET
           [PlayerId] = @PlayerId
           ,[LastSeen] = @LastSeen
WHERE
			[PlayerId] = @PlayerId
     
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerVictimsSelectByPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerVictimsSelectByPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Player Victims Select By PlayerId
-- =============================================
CREATE PROCEDURE PlayerVictimsSelectByPlayerId

			@Attacker_playerId varchar(30)
	
AS
BEGIN

DECLARE @Victim_playerId varchar(30)

DECLARE @PlayerName varchar(50),
		@Kills int,
		@Deaths int

DECLARE @tempPlayers TABLE
(
	PlayerName varchar(50),
	Kills int,
	Deaths int,
	KillDeathRatio varchar(5),
	Victim_playerId varchar(30)
)

DECLARE c1 CURSOR FAST_FORWARD
FOR
SELECT Victim_playerId from KILLS
WHERE Attacker_playerId = @Attacker_playerId
GROUP BY Victim_playerId

OPEN c1

FETCH NEXT FROM c1
INTO @Victim_playerId


WHILE @@FETCH_STATUS = 0
BEGIN

	SELECT @PlayerName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @Victim_playerId 
	GROUP BY PlayerName

	SELECT @Kills = Count(KillId) FROM Kills WHERE Victim_playerId = @Victim_playerId
						AND Attacker_playerId = @Attacker_playerId
	SELECT @Deaths = COUNT(KillId) FROM Kills WHERE Victim_playerId = @Attacker_playerId
						AND Attacker_playerId = @Victim_playerId

	INSERT INTO @tempPlayers VALUES (@PlayerName, @Kills, @Deaths, '', @Victim_playerId)

	FETCH NEXT FROM c1
	INTO @Victim_playerId

END

CLOSE c1
DEALLOCATE c1

SELECT PlayerName, Kills, Deaths, KillDeathRatio, Victim_playerId FROM @tempPlayers


END
GO

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerVictimsSelectByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerVictimsSelectByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 10/30/08
-- Description:	Player Victims Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE PlayerVictimsSelectByPlayerIdLogId

			@Attacker_playerId varchar(30),
			@LogId varchar(8000)
	
AS
BEGIN

DECLARE @Victim_playerId varchar(30)

DECLARE @PlayerName varchar(50),
		@Kills int,
		@Deaths int

DECLARE @tempPlayers TABLE
(
	PlayerName varchar(50),
	Kills int,
	Deaths int,
	KillDeathRatio varchar(5),
	Victim_playerId varchar(30)
)

DECLARE c1 CURSOR FAST_FORWARD
FOR
SELECT Victim_playerId from KILLS
WHERE Attacker_playerId = @Attacker_playerId
GROUP BY Victim_playerId

OPEN c1

FETCH NEXT FROM c1
INTO @Victim_playerId


WHILE @@FETCH_STATUS = 0
BEGIN

	SELECT @PlayerName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @Victim_playerId 
	GROUP BY PlayerName


	DECLARE @ResultSet TABLE (resultCount int)
	DECLARE @SQL varchar(600)

	-- KILLS
	SET @SQL = 'SELECT COUNT(KillId) FROM Kills WHERE Victim_PlayerId = ''' + @Victim_playerId + '''
					AND Attacker_playerId = ''' + @Attacker_playerId + '''
				AND LogId IN (' + @LogId + ')'

	DELETE FROM @ResultSet
	INSERT INTO @ResultSet EXEC(@SQL)
	SELECT @Kills = resultCount FROM @ResultSet

	-- Deaths
	SET @SQL = 'SELECT COUNT(KillId) FROM Kills WHERE Victim_PlayerId = ''' + @Attacker_playerId + '''
					AND Attacker_playerId = ''' + @Victim_playerId + '''
					AND LogId IN (' + @LogId + ')'

	DELETE FROM @ResultSet
	INSERT INTO @ResultSet EXEC(@SQL)
	select @Deaths = resultCount FROM @ResultSet



	IF (@Kills > 0 OR @Deaths > 0)
	BEGIN
		INSERT INTO @tempPlayers VALUES (@PlayerName, @Kills, @Deaths, '', @Victim_playerId)
	END

	FETCH NEXT FROM c1
	INTO @Victim_playerId

END

CLOSE c1
DEALLOCATE c1

SELECT PlayerName, Kills, Deaths, KillDeathRatio, Victim_playerId FROM @tempPlayers


END
GO

-- PlayerVictimsSelectByPlayerIdLogId 'STEAM_0:1:100283', '1,3'

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerWeaponStatsSelectByPlayerId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerWeaponStatsSelectByPlayerId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Player Victims Select By PlayerId
-- =============================================
CREATE PROCEDURE PlayerWeaponStatsSelectByPlayerId

			@Attacker_playerId varchar(30),
			@WeaponId int
	
AS
BEGIN


DECLARE @WeaponName varchar(50),
		@Kills int,
		@Deaths int,
		@KillDeathRatio varchar(8)

SELECT @Kills = COUNT(KillId) from [Kills]	WHERE Attacker_PlayerId = @Attacker_playerId AND Attacker_weaponId = @WeaponId
SELECT @Deaths = COUNT(KillId) from [Kills]	WHERE Victim_PlayerId = @Attacker_playerId AND Attacker_weaponId = @WeaponId
SELECT @WeaponName = WeaponFullName from [Weapons] WHERE WeaponId = @WeaponId
SET @KillDeathRatio = ''


SELECT @WeaponName AS [Weapon], @Kills AS [Kills], @Deaths AS [Deaths], 
@KillDeathRatio AS [KillDeathRatio], @WeaponId as [WeaponId]

END
GO

--  PlayerWeaponStatsSelectByPlayerId 'STEAM_0:1:16198379',1


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[RevengesInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.RevengesInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Revenges Table Insert
-- =============================================
CREATE PROCEDURE RevengesInsert

		   @RevengeId int,
           @RevengeTime datetime,
           @Attacker_playerId varchar(30),
           @Victim_playerId varchar(30),
           @RoundId int,
           @MiniRoundId int,
           @MapId int,
           @LogId int,
           @DateTime datetime
	

AS
BEGIN

INSERT INTO [Revenges]
           ([RevengeId]
           ,[RevengeTime]
           ,[Attacker_playerId]
           ,[Victim_playerId]
           ,[RoundId]
           ,[MiniRoundId]
           ,[MapId]
           ,[LogId]
           ,[DateTime])
     VALUES
           (@RevengeId,
           @RevengeTime,
           @Attacker_playerId,
           @Victim_playerId,
           @RoundId,
           @MiniRoundId,
           @MapId,
           @LogId,
           @DateTime)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[RevengesSelectCountByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.RevengesSelectCountByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Revenges Table Select Count By PlayerId and LogId
-- =============================================
CREATE PROCEDURE RevengesSelectCountByPlayerIdLogId

            @LogId int,
			@PlayerId varchar(30),
			@RevengesCount int = 0 OUTPUT
	
AS

BEGIN

select @RevengesCount  = COUNT(RevengeId) FROM Revenges
		WHERE LogId = @LogId
		AND Attacker_PlayerId = @PlayerId
		


END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[RoundsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.RoundsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Rounds Table Insert
-- =============================================
CREATE PROCEDURE RoundsInsert

           @RoundId int,
           @WinningTeamId int,
           @LosingTeamId int,
           @WinningTeamScore int,
           @LosingTeamScore int,
           @RoundLengthSeconds int,
           @LogId int,
           @EndTime datetime
	

AS
BEGIN

INSERT INTO [Rounds]
           ([RoundId]
           ,[WinningTeamId]
           ,[LosingTeamId]
           ,[WinningTeamScore]
           ,[LosingTeamScore]
           ,[RoundLengthSeconds]
           ,[LogId]
           ,[EndTime])
     VALUES
           (@RoundId,
           @WinningTeamId,
           @LosingTeamId,
           @WinningTeamScore,
           @LosingTeamScore,
           @RoundLengthSeconds,
           @LogId,
           @EndTime)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SuicidesInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.SuicidesInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Suicides Table Insert
-- =============================================
CREATE PROCEDURE SuicidesInsert

			@SuicideId int,
            @PlayerId varchar(30),
            @ClassId int,
            @WeaponId int,
            @DateTime datetime,
            @LogId int,
            @RoundId int,
            @MiniRoundId int,
            @MapId int,
            @Pos_x float,
            @Pos_y float,
            @Pos_z float
	

AS
BEGIN

INSERT INTO [Suicides]
           ([SuicideId]
           ,[PlayerId]
           ,[ClassId]
           ,[WeaponId]
           ,[DateTime]
           ,[LogId]
           ,[RoundId]
           ,[MiniRoundId]
           ,[MapId]
           ,[Pos_x]
           ,[Pos_y]
           ,[Pos_z])
     VALUES
           (@SuicideId,
            @PlayerId,
            @ClassId,
            @WeaponId,
            @DateTime,
            @LogId,
            @RoundId,
            @MiniRoundId,
            @MapId,
            @Pos_x,
            @Pos_y,
            @Pos_z)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[TeamsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.TeamsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Teams Table Insert
-- =============================================
CREATE PROCEDURE TeamsInsert

			@TeamShortName varchar(50),
            @TeamFullName varchar(50)
	

AS
BEGIN

INSERT INTO [Teams]
           ([TeamShortName]
           ,[TeamFullName])
     VALUES
           (@TeamShortName,
            @TeamFullName)
END
GO


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UsersSelect]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.UsersSelect
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/04/08
-- Description:	Users Table Select
-- =============================================
CREATE PROCEDURE UsersSelect


AS
BEGIN

SELECT [Login], Email, Admin, LastLogin
FROM Users

END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UsersSelectByLogin]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.UsersSelectByLogin
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/04/08
-- Description:	Users Table Select By login
-- =============================================
CREATE PROCEDURE UsersSelectByLogin

	@Login varchar(30)

AS
BEGIN

SELECT [Login], Email, Admin, LastLogin
FROM Users
WHERE [Login] = @Login

END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[UsersUpdateLastLogin]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.UsersUpdateLastLogin
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/04/08
-- Description:	Users Table Update LastLogin
-- =============================================
CREATE PROCEDURE UsersUpdateLastLogin

	@Login varchar(30),
	@LastLogin datetime

AS
BEGIN

UPDATE Users
SET LastLogin = @LastLogin
WHERE [Login] = @Login

END
GO



USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[WeaponKillsTop25Select]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.WeaponKillsTop25Select
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/24/08
-- Description:	Weapon Kills Top 25 Select
-- =============================================
CREATE PROCEDURE WeaponKillsTop25Select

AS

BEGIN

	SELECT TOP 25 Attacker_playerId AS [PlayerId], 
			Count(Attacker_playerId) AS [Kills],
			0 AS [Deaths],
			0.0 AS [KillDeathRatio]
	FROM Kills
	WHERE Attacker_classId = 2
	GROUP BY Attacker_playerId
	ORDER BY [Kills] DESC


select * from kills where attacker_playerid = 'STEAM_0:0:5026980'
and attacker_classid = 2

END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[WeaponsInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.WeaponsInsert
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 08/29/08
-- Description:	Weapons Table Insert
-- =============================================
CREATE PROCEDURE WeaponsInsert

            @WeaponId int,
            @WeaponShortName varchar(50),
            @WeaponFullName varchar(50),
            @ClassId int

AS
BEGIN

INSERT INTO [Weapons]
           ([WeaponId]
           ,[WeaponShortName]
           ,[WeaponFullName]
           ,[ClassId])
     VALUES
           (@WeaponId,
            @WeaponShortName,
            @WeaponFullName,
            @ClassId)
END
GO





USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[WeaponsSelectKillTop5ByWeaponName]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.WeaponsSelectKillTop5ByWeaponName
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/03/08
-- Description:	Weapons Table Select - Top 5 killers per weapon
-- =============================================
CREATE PROCEDURE WeaponsSelectKillTop5ByWeaponName

            @WeaponName varchar(50)
	
AS

BEGIN

SELECT  TOP 5  PlayerName, count(killid) as [Kills], PlayerId FROM Kills
INNER JOIN PlayerNameList ON attacker_playerid = PlayerNameList.PlayerID
 where 
Attacker_Weaponid = (SELECT WeaponId FROM Weapons WHERE WeaponShortName = @WeaponName)
group by Attacker_PlayerId, PlayerName, PlayerId
order by [Kills] DESC

END
GO



--  WeaponsSelectKillTop5ByWeaponName 'tf_projectile_rocket'


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[WeaponsSelectKillTop5MeleeWeapons]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.WeaponsSelectKillTop5MeleeWeapons
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/12/08
-- Description:	Weapons Table Select - Top 5 Killers with Melee Weapons
-- =============================================
CREATE PROCEDURE WeaponsSelectKillTop5MeleeWeapons

AS

BEGIN

SELECT  TOP 5  PlayerName, count(killid) as [Kills], PlayerId FROM Kills
INNER JOIN PlayerNameList ON attacker_playerid = PlayerNameList.PlayerID
 where 
Attacker_Weaponid IN (SELECT WeaponId FROM Weapons WHERE WeaponShortName 
IN (
'bat',
'shovel',
'axe',
'axtinguisher',
'bottle',
'fists',
'gloves',
'wrench',
'knife',
'bonesaw',
'ubersaw',
'club'
))

group by Attacker_PlayerId, PlayerName, PlayerId
order by [Kills] DESC

END
GO

--  WeaponsSelectKillTop5MeleeWeapons


USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[WeaponsSelectTop25Stats]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.WeaponsSelectTop25Stats
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/24/08
-- Description:	Weapons Table Top 25 Stats Select
-- =============================================
CREATE PROCEDURE WeaponsSelectTop25Stats

	@WeaponId int

AS

BEGIN


	DECLARE @PlayerId varchar(30)

DECLARE @PlayerName varchar(50),
		@Kills int,
		@Deaths int,
		@HeadShotPerctage float


DECLARE @tempPlayers TABLE
(
	PlayerName varchar(50),
	Kills int,
	Deaths int,
	KillDeathRatio varchar(5),
	HeadShotPercentage float,
	PlayerId varchar(30)
)

DECLARE c1 CURSOR FAST_FORWARD
FOR

SELECT Top 25 COUNT(KillId) [Kills], Attacker_playerId
FROM Kills
WHERE Attacker_weaponId = @WeaponId
GROUP BY Attacker_playerId
ORDER BY [Kills] Desc

OPEN c1

FETCH NEXT FROM c1
INTO @Kills, @PlayerId


WHILE @@FETCH_STATUS = 0
BEGIN

	SELECT @PlayerName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @PlayerId 
	GROUP BY PlayerName

	
	SELECT @Deaths = COUNT(KillId) FROM Kills WHERE Victim_playerId = @PlayerId
						AND Attacker_weaponId = @WeaponId

	INSERT INTO @tempPlayers VALUES (@PlayerName, @Kills, @Deaths, 0.0, 0.0, @PlayerId)

	FETCH NEXT FROM c1
	INTO @Kills, @PlayerId

END

CLOSE c1
DEALLOCATE c1

SELECT PlayerName, Kills, Deaths, KillDeathRatio, HeadShotPercentage, PlayerId FROM @tempPlayers
ORDER BY Kills DESC, KillDeathRatio DESC, Deaths DESC
	
END
GO


-- WeaponsSelectTop25Stats 13

USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[WeaponsStatsSelect]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.WeaponsStatsSelect
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/24/08
-- Description:	Weapons Table Stats Select
-- =============================================
CREATE PROCEDURE WeaponsStatsSelect

AS

BEGIN

	SELECT COUNT(Kills.Attacker_weaponId) AS [Kills], 0 AS [Deaths], 0.0 AS [KillDeathRatio], 0.0 AS [Headshot Percentage], WeaponId, WeaponShortName, WeaponFullName, ClassId FROM Weapons
	LEFT OUTER JOIN Kills on Kills.Attacker_weaponId = Weapons.WeaponId
	GROUP BY WeaponId, WeaponShortName, WeaponFullName, ClassId
	ORDER BY [Kills] DESC
	
END
GO




USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[DeleteAllLogDataByLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.DeleteAllLogDataByLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 09/30/08
-- Description:	Deletes all logged table data by LogId
-- =============================================
CREATE PROCEDURE DeleteAllLogDataByLogId

	@LogId int

AS
BEGIN

	DELETE FROM Assists WHERE LogId = @LogId
	DELETE FROM CaptureBlocks WHERE LogId = @LogId
	DELETE FROM CaptureList WHERE LogId = @LogId
	DELETE FROM Captures WHERE LogId = @LogId
	DELETE FROM ChatLog WHERE LogId = @LogId
	DELETE FROM Destructions WHERE LogId = @LogId
	DELETE FROM Dominations WHERE LogId = @LogId
	DELETE FROM Engineering WHERE LogId = @LogId
	DELETE FROM Flag WHERE LogId = @LogId
	DELETE FROM Kills WHERE LogId = @LogId
	DELETE FROM LogFiles WHERE LogId = @LogId
	DELETE FROM Matches WHERE LogId = @LogId
	DELETE FROM MatchPlayerList WHERE LogId = @LogId
	DELETE FROM MedicCharge WHERE LogId = @LogId
	DELETE FROM PlayerNameList WHERE LogId = @LogId
	DELETE FROM Players WHERE LogId = @LogId
	DELETE FROM Revenges WHERE LogId = @LogId
	DELETE FROM Rounds WHERE LogId = @LogId
	DELETE FROM Suicides WHERE LogId = @LogId


END
GO


